﻿
#region "命名空间"

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using ZN.Entities;

#endregion

namespace ZN.Common {
    /// <summary>
    /// 基于平台,封装了包含 Sql 脚本参数化和执行脚本的相关方法
    /// </summary>
    /// <remarks>
    ///  简化参数化的Sql语句,并将不同数据库连接提供者的参数格式进行统一(这里统一使用Oracle的参数格式)
    ///  支持自动转换参数类型(OracleClient,OleDb,SqlClient)
    /// </remarks>
    public class Sql {

        #region "私有成员"

        private string _SqlString;
        private string _DataSourceName=string.Empty;
        private CommandType _CommandType;
        private DbHelpClassTypes DbHelpType;
        private List<IDataParameter> _Parameters=new List<IDataParameter>();

        #endregion

        #region "公有属性"

        /// <summary>
        /// 获取Sql 字符串
        /// </summary>
        public string SqlString {
            get { return _SqlString; }
        }
        /// <summary>
        /// 获取当前语句中的参数数组
        /// </summary>
        public IDataParameter[] Parameters {
            get { return _Parameters.ToArray(); }
        }
        public IDbHelp DbHelp {
            get {
                if (!string.IsNullOrEmpty(_DataSourceName))
                    return DbFactory.Instance.GetDbHelp(_DataSourceName);
                return DbFactory.Instance.GetDbHelp();
            }
        }
        #endregion

        #region "构造函数"
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="commandType">执行命令类型</param>
        /// <param name="sql">Sql脚本</param>
        private Sql(CommandType commandType, string sql)
            : this(string.Empty, commandType, sql) {
        }

        public Sql(string dataSourceName, CommandType commandType, string sql) {
            this._DataSourceName=dataSourceName;
            this.DbHelpType=DbFactory.Instance.GetDbHelp().DataSourceInfo.DbHelpType;
            this._SqlString=ConvertSqlPlaceHolderOfSqlStatement(sql);
            this._CommandType=commandType;

        }
        #endregion

        #region "公有方法"

        /// <summary>
        /// 创建一个Sql对象
        /// </summary>
        /// <param name="dataSourceName">数据源名称</param>
        /// <param name="commandType">执行命令类型</param>
        /// <param name="sql">要执行的Sql语句</param>
        /// <returns>Sql对象</returns>
        public static Sql Create(string dataSourceName, CommandType commandType, string sql) {
            return new Sql(dataSourceName, commandType, sql);
        }
        /// <summary>
        /// 创建一个Sql对象
        /// </summary>
        /// <param name="commandType">执行命令类型</param>
        /// <param name="sql">要执行的Sql语句</param>
        /// <returns>Sql对象</returns>
        public static Sql Create(CommandType commandType, string sql) {
            return new Sql(commandType, sql);
        }
        /// <summary>
        /// 创建一个Sql对象
        /// </summary>
        /// <param name="dataSourceName">数据源名称</param>
        /// <param name="sql">要执行的Sql语句</param>
        /// <returns></returns>
        public static Sql Create(string dataSourceName, string sql) {
            return new Sql(dataSourceName, CommandType.Text, sql);
        }
        /// <summary>
        ///  创建一个Sql对象
        /// </summary>
        /// <param name="sql">要执行的Sql语句</param>
        /// <returns>Sql对象</returns>
        public static Sql Create(string sql) {
            return new Sql(CommandType.Text, sql);
        }
        /// <summary>
        /// 设置当前Sql参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramValue">参数值</param>
        /// <returns></returns>
        public Sql Set(string paramName, object paramValue) {
            IDataParameter parameter= DbFactory.Instance.GetDbCommandHelp().CreateParameter(paramName, paramValue);
            Set(parameter);
            return this;
        }
        /// <summary>
        /// 设置参数
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public Sql Set(IDataParameter parameter) {
            parameter.ParameterName=ConvertSqlPlaceHolderOfSqlStatement(parameter.ParameterName);
            _Parameters.Add(parameter);
            return this;
        }
        /// <summary>
        /// 批量设置参数
        /// </summary>
        /// <param name="args"></param>
        /// <returns></returns>
        public Sql SetParams(IDictionary args) {
            foreach (var item in args.Keys) {
                Set(item.ToString(), args[item]);
            }
            return this;
        }
        /// <summary>
        /// 移除一个参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <returns></returns>
        public Sql Remove(string paramName) {
            Remove(_Parameters.Find(t => t.ParameterName.Equals(paramName)));
            return this;
        }

        public Sql Remove(IDataParameter parameter) {
            _Parameters.Remove(parameter);
            return this;
        }
        /// <summary>
        /// 清除参数
        /// </summary>
        /// <returns></returns>
        public Sql Clear() {
            _Parameters.Clear();
            return this;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public object ForScalar() {
            return DbHelp.ExecuteScalar(_SqlString, _CommandType, _Parameters.ToArray());
        }

        public T ForScalar<T>() {
            return ForScalar<T>(default(T));
        }

        public T ForScalar<T>(T defaultValue) {
            object result= ForScalar();
            if (result==null) {
                return defaultValue;
            }
            return Utils.Cast<T>(result, defaultValue);
        }
        /// <summary>
        ///  将数据转换为指定对象 List<T></T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> ForList<T>() where T : EntityBase {
            //CBO 中包含reader 关闭的代码
            IDataReader reader= DbHelp.GetDataReader(_SqlString, _CommandType, _Parameters.ToArray());
            return CBO.FillCollection<T>(reader);
        }
        /// <summary>
        /// 将数据转换为指定对象 T
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T ForObject<T>() where T : EntityBase {
            //CBO 中包含reader 关闭的代码
            IDataReader reader= DbHelp.GetDataReader(_SqlString, _CommandType, _Parameters.ToArray());
            return CBO.FillObject<T>(reader);
        }
        /// <summary>
        /// 将数据转换为DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ForDataTable() {
            DataTable resultTable= DbHelp.GetDataTable(_SqlString, _CommandType, _Parameters.ToArray());
            return resultTable;
        }
        /// <summary>
        /// 将数据转换为数组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> ForArray<T>() {

            return (from table in ForDataTable().AsEnumerable() select table.Field<T>(0)).ToList<T>();
        }
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <returns></returns>
        public DataRow ForDataRow() {
            DataTable resultTable=DbHelp.GetDataTable(_SqlString, _CommandType, _Parameters.ToArray());
            if (resultTable.Rows.Count>0) {
                return resultTable.AsEnumerable().FirstOrDefault();
            }
            return null;
        }
        /// <summary>
        /// 执行语句不查询
        /// </summary>
        /// <returns>执行语句影响的行数</returns>
        public int ForExecute() {
            return ForExecute(null);
        }
        /// <summary>
        /// 执行语句不查询
        /// </summary>
        /// <param name="afterExecute">执行后动作,主要用于取存储过程中输出参数的值</param>
        /// <returns>执行语句影响的行数</returns>
        public int ForExecute(Action<IEnumerable<IDataParameter>> afterExecute) {
            int result= DbHelp.ExecuteNonQuery(_SqlString, _CommandType, _Parameters.ToArray());
            if (afterExecute!=null) {
                afterExecute(_Parameters);
            }
            return result;
        }

        #endregion

        #region "私有方法"
        /// <summary>
        /// 根据不同的数据库连接方式,转换参数中的占位符
        /// </summary>
        /// <param name="parameterName">SQL参数名</param>
        /// <returns>转后的参数</returns>
        private string ConvertSqlPlaceHolderOfSqlParameter(string parameterName) {
            switch (DbHelpType) {
                case DbHelpClassTypes.OleDbHelp:
                    return parameterName.TrimStart(new char[] { '@', ':' });
                case DbHelpClassTypes.OracleDbHelp:
                    return ":"+ parameterName.TrimStart(new char[] { '@', ':' });
                case DbHelpClassTypes.SqlDbHelp:
                    return "@"+ parameterName.TrimStart(new char[] { '@', ':' });
            }
            return parameterName;
        }
        /// <summary>
        /// 根据不同的数据库连接方式,转换sql中的占位符
        /// </summary>
        /// <param name="sql">Sql脚本</param>
        /// <returns>转后的Sql</returns>
        private string ConvertSqlPlaceHolderOfSqlStatement(string sql) {
            Regex regex=new Regex(@":(\w+)");
            switch (DbHelpType) {
                case DbHelpClassTypes.OleDbHelp:
                    return regex.Replace(sql, "?");
                //case DbHelpClassTypes.OracleDbHelp:
                //    return regex.Replace(sql, ":$1");
                case DbHelpClassTypes.SqlDbHelp:
                    return regex.Replace(sql, "@$1");
            }
            return sql;
        }

        #endregion
    }
}
